/*-------------------<-- Start of Description-->---------------------\ | The function will need 2 or more than 2 datasets to do the | | comparison of the variable names, it will print the variable | | names in common between the datasets to the output and the log | | window. | |---------------------<-- End of Description-->----------------------| |--------------------------------------------------------------------| |------------<-- Start of Files or Arguments Needed-->---------------| | Argument: | | indata: input data set; | | compare: common - variables in common between any two data; | | diff - variables different between any two data; | | both - variables in common or different; | | all - variables in common or different; | | default- both; | |---------------<-- End of Files Arguments Needed-->-----------------| |--------------------------------------------------------------------| |------------------<-- Start of Files Created-->---------------------| | Example: %vcompare(indata=one two three, compare=common); / | | %vcompare(one two three); | | %vcompare(one two,compare=diff common); | | Usage: %vcompare(indata=, compare=); | \-------------------<-- End of Files Created-->---------------------*/ %macro vcompare/parmbuff; /*--------------------------------------------\ | Copy Right: Duo Zhou; | | Created: 8-30-2001 7:17pm; | | Modified: 11-12-2001 9:14pm; | | Purpose: Compare the variable names between| | 2 or more datasets; | \--------------------------------------------*/ %local indata compare; proc format; value typef (multilabel) 1="Num" 2="Char"; run; %local ndsn indata tempdata _i_ _j_ _k_ compbuff compare _tmplast_; %let _tmplast_=&syslast; %let dsnbuff=%qscan(&syspbuff,1,%str((),)); %let compbuff=%qscan(&syspbuff,2,%str((),)); %let linesize = %SYSFUNC(GETOPTION(linesize)); %if (%index(%quote(&syspbuff),%quote(=))) %then %do; %if (%index(%quote(&dsnbuff),%quote(=))) %then %do; %if (%index(%quote(%upcase(%sysfunc(compress(%quote(&dsnbuff))))),%quote(DATA=))) %then %do; %let indata=%qscan(&dsnbuff,2,%str(=)); %if (%index(%quote(&compbuff),%quote(=))) %then %do; %if (not %index(%quote(%upcase(%sysfunc(compress(%quote(&compbuff))))),%quote(COMPARE=))) %then %do; %put ==> Alert! Keyword parameter "%qscan(&compbuff,1,%str(=))" is not defined!; %end; %else %do; %let compare=%qscan(&compbuff,2,%str(=)); %end; %end; %else %do; %let compare=&compbuff; %end; %end; %else %if (%index(%quote(%upcase(%sysfunc(compress(%quote(&dsnbuff))))),%quote(COMPARE=))) %then %do; %let compare=%qscan(&dsnbuff,2,%str(=)); %if (%index(%quote(&compbuff),%quote(=))) %then %do; %if (not %index(%quote(%upcase(%sysfunc(compress(%quote(&compbuff))))),%quote(DATA=))) %then %do; %put ==> Alert! Keyword parameter "%qscan(&compbuff,1,%str(=))" is not defined!; %end; %else %do; %let indata=%qscan(&compbuff,2,%str(=)); %end; %end; %else %do; %let indata=&compbuff; %end; %end; %else %put ==> Alert! Keyword parameter "%qscan(&dsnbuff,1,%str(=))" is not defined!; %end; %else %if (%index(%quote(&compbuff),%quote(=))) %then %do; %if (%index(%quote(%upcase(%sysfunc(compress(%quote(&compbuff))))),%quote(DATA=))) %then %do; %let indata=%qscan(&compbuff,2,%str(=)); %let compare=&dsnbuff; %end; %else %if (%index(%quote(%upcase(%sysfunc(compress(%quote(&compbuff))))),%quote(COMPARE=))) %then %do; %let indata=&dsnbuff; %let compare=%qscan(&compbuff,2,%str(=)); %end; %else %do; %put ==> Alert! Keyword parameter "%qscan(&compbuff,1,%str(=))" is not defined!; %end; %end; %end; %else %do; %let indata=&dsnbuff; %let compare=&compbuff; %end; %if (not %index(%quote(%upcase(&compare)),COMMON)) and (not %index(%quote(%upcase(&compare)),DIFF)) %then %do; %let compare=both; %put --> Note: You forgot to provide me a "compare" method: the variables in common or different between; %put --> the datasets! I will take the assumption that you want to compare in both ways (common ; %put --> and different).; %end; %if (%quote(&indata) eq) %then %do; %put ==> Alert! No dataset is to be compared! %goto finish; %end; %let ndsn=1; %let dsn&ndsn=%qscan(&indata,&ndsn,%str( ,)); %do %while(&&dsn&ndsn ne); %let ndsn=%eval(&ndsn+1); %let dsn&ndsn=%qscan(&indata,&ndsn,%str( ,)); %end; %let ndsn=%eval(&ndsn-1); %if &ndsn<=1 %then %do; %put ==> Alert! You just provided one data set, I can do nothing with it.; %end; %else %do; %if &ndsn>2 %then %do; %put --> Note: You have just given me &ndsn data set. To do the pairwise; %put --> comparison, it could take extensive time.; %end; %if ((%index(%upcase(&compare),COMMON)) and (%index(%upcase(&compare),DIFF))) or (%index(%upcase(&compare),BOTH)) %then %do; %put --> Note: I will list all the variables in common and the variables different between any two; %put --> of the &ndsn data sets.; %end; %else %if ((%index(%upcase(&compare),COMMON)) and (not %index(%upcase(&compare),DIFF))) %then %do; %put --> Note: I will list all the variables in common between any two of the &ndsn data sets.; %end; %else %if ((not %index(%upcase(&compare),COMMON)) and (%index(%upcase(&compare),DIFF))) %then %do; %put --> Note: I will list all the variables different between any two of the &ndsn data sets.; %end; %do _i_=1 %to &ndsn; proc contents data=&&dsn&_i_ noprint out=_tmp&_i_(keep=name label type length format formatl formatd) memtype=(data view); run; %if (not %sysfunc(exist(_tmp&_i_))) %then %do; %put ==> Alert! Cannot find "&&dsn&_i_"!; %goto finish; %end; proc sort data=_tmp&_i_; by name; run; %end; %do _j_=1 %to %eval(&ndsn-1); %do _k_=%eval(&_j_+1) %to &ndsn; %let Names_InCommon_d1_d2=; %let Names_Ind1_NotInd2=; %let Names_NotInd1_Ind2=; %if (%index(%upcase(&compare),COMMON)) or (%index(%upcase(&compare),BOTH)) or (%index(%upcase(&compare),ALL))%then %do; proc sql noprint;%let Names_InCommon_d1_d2=; %let Names_InCommonTypeDiff_d1_d2=; /*create table Names_InCommon_d&_j_._d&_k_ as*/ select t1.name into:Names_InCommon_d1_d2 separated by ", " from _tmp&_j_ as t1, _tmp&_k_ as t2 where lowcase(t1.name)=lowcase(t2.name) order by t1.name; select t1.name into:Names_InCommonTypeDiff_d1_d2 separated by ", " from _tmp&_j_ as t1, _tmp&_k_ as t2 where (lowcase(t1.name)=lowcase(t2.name)) and (t1.type ne t2.type) order by t1.name; quit; proc sql; create table Names_InCommon_Data&_j_._Data&_k_ as select t1.name label="Variable", t1.label as label1 label="VarLabel in &&dsn&_j_", t2.label as label2 label="VarLabel in &&dsn&_k_", t1.type as type1 format=typef. label="VarType in &&dsn&_j_", t2.type as type2 format=typef. label="Type in &&dsn&_k_", t1.length as length1 label="VarLength in &&dsn&_j_", t2.length as length2 label="VarLength in &&dsn&_k_", case when t1.format = ' ' and t1.formatl<1 and t1.formatd< 1 then trimn(left(trimn(left(t1.format))||trimn(left(put(t1.formatl, 10.))))) when (t1.format ne ' ' or t1.formatl>=1) and t1.formatd< 1 then trimn(left(trimn(left(t1.format))||trimn(left(put(t1.formatl, 10.))))||'.') when t1.formatd>=1 then trimn(left(trimn(left(t1.format))||trimn(left(put(t1.formatl, 10.)))||'.'||trimn(left(put(t1.formatd, 10.))))) end as format1 label="VarFormat in &&dsn&_j_", case when t2.format = ' ' and t2.formatl<1 and t2.formatd< 1 then trimn(left(trimn(left(t2.format))||trimn(left(put(t2.formatl, 10.))))) when (t2.format ne ' ' or t2.formatl>=1) and t2.formatd< 1 then trimn(left(trimn(left(t2.format))||trimn(left(put(t2.formatl, 10.))))||'.') when t2.formatd>=1 then trimn(left(trimn(left(t2.format))||trimn(left(put(t2.formatl, 10.)))||'.'||trimn(left(put(t2.formatd, 10.))))) end as format2 label="VarFormat in &&dsn&_k_" from _tmp&_j_ as t1, _tmp&_k_ as t2 where lowcase(t1.name)=lowcase(t2.name) order by t1.name; quit; proc print data=Names_InCommon_Data&_j_._Data&_k_ label; title "%center(%initcaps(Variables In Common Between Data &&dsn&_j_ and Data &&dsn&_k_))";run; data _null_; file print; nobs1=%nobs(&&dsn&_j_); nvars1=%nvars(&&dsn&_j_); nobs2=%nobs(&&dsn&_k_); nvars2=%nvars(&&dsn&_k_); nvarcom=%nobs(Names_InCommon_Data&_j_._Data&_k_); put /; put @20 @1 "%justify(%initcaps(Summary of Data: &&dsn&_j_ and &&dsn&_k_), justify=center)" /; put &linesize*"-"/; put @5 "Number of observations in data &&dsn&_j_" @(49+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nobs1 /; put @5 "Number of variables in data &&dsn&_j_" @(49+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nvars1 /; put @5 "Number of observations in data &&dsn&_k_" @(49+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nobs2 /; put @5 "Number of variables in data &&dsn&_k_" @(49+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nvars2 /; put @5 "Number of variables in common" @(49+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nvarcom /; run; %put %_repeat('-',&linesize); %if (%quote(&Names_InCommon_d1_d2) ne) %then %do; %put --> Note: Variables in common between %upcase(&&dsn&_j_) and %upcase(&&dsn&_k_): ; %put --> %upcase(&Names_InCommon_d1_d2)%str(;); %if (%quote(&Names_InCommonTypeDiff_d1_d2) ne) %then %do; %put --> Variables in common, types are different between %upcase(&&dsn&_j_) and %upcase(&&dsn&_k_): ; %put --> %upcase(&Names_InCommonTypeDiff_d1_d2)%str(;); %end; %else %do; %put --> and they have the same type too%str(;); %end; %put --> please see output for LABEL, FORMAT, LENGTH and TYPE details.; %end; %else %do; %put --> Note: There are no Variables In Common Between %upcase(&&dsn&_j_) and %upcase(&&dsn&_k_).; %end; %put %_repeat('-',&linesize); /*** cleanup the temp datasets ***/ proc datasets library=work nolist; delete Names_InCommon_data&_j_._data&_k_; run;quit; %end; %if (%index(%upcase(&compare),DIFF)) or (%index(%upcase(&compare),BOTH)) or (%index(%upcase(&compare),ALL)) %then %do; proc sql noprint; %let Names_Ind1_NotInd2=;%let Names_NotInd1_Ind2=; create table Names_InData&_j_._NotInData&_k_ as select t1.name label="Variable in &&dsn&_j_", t1.label as label label="VarLabel in &&dsn&_j_", t1.type as type format=typef. label="VarType in &&dsn&_j_", t1.length as length label="VarLength in &&dsn&_j_", case when t1.format = ' ' and t1.formatl<1 and t1.formatd< 1 then trimn(left(trimn(left(t1.format))||trimn(left(put(t1.formatl, 10.))))) when (t1.format ne ' ' or t1.formatl>=1) and t1.formatd< 1 then trimn(left(trimn(left(t1.format))||trimn(left(put(t1.formatl, 10.))))||'.') when t1.formatd>=1 then trimn(left(trimn(left(t1.format))||trimn(left(put(t1.formatl, 10.)))||'.'||trimn(left(put(t1.formatd, 10.))))) end as format label="VarFormat in &&dsn&_j_" from _tmp&_j_ as t1 where lowcase(t1.name) not in (select lowcase(name) as name from _tmp&_k_) order by t1.name; select t1.name into:Names_Ind1_NotInd2 separated by ", " from _tmp&_j_ as t1 where lowcase(t1.name) not in (select lowcase(name) as name from _tmp&_k_); create table Names_NotInData&_j_._InData&_k_ as select t2.name label="Variable in &&dsn&_k_", t2.label as label label="VarLabel in &&dsn&_k_", t2.type as type format=typef. label="VarType in &&dsn&_k_", t2.length as length label="VarLength in &&dsn&_k_", case when t2.format = ' ' and t2.formatl<1 and t2.formatd< 1 then trimn(left(trimn(left(t2.format))||trimn(left(put(t2.formatl, 10.))))) when (t2.format ne ' ' or t2.formatl>=1) and t2.formatd< 1 then trimn(left(trimn(left(t2.format))||trimn(left(put(t2.formatl, 10.))))||'.') when t2.formatd>=1 then trimn(left(trimn(left(t2.format))||trimn(left(put(t2.formatl, 10.)))||'.'||trimn(left(put(t2.formatd, 10.))))) end as format label="VarFormat in &&dsn&_k_" from _tmp&_k_ as t2 where lowcase(t2.name) not in (select lowcase(name) as name from _tmp&_j_) order by t2.name; select t2.name into:Names_NotInd1_Ind2 separated by ", " from _tmp&_k_ as t2 where lowcase(t2.name) not in (select lowcase(name) as name from _tmp&_j_); quit; proc print data=Names_InData&_j_._NotInData&_k_ label; title "%center(%initcaps(Variables In Data &&dsn&_j_ But Not In Data &&dsn&_k_))";run; proc print data=Names_NotInData&_j_._InData&_k_ label; title "%center(%initcaps(Variables Not In Data &&dsn&_j_ But In Data &&dsn&_k_))";run; data _null_; file print; nobs1=%nobs(&&dsn&_j_); nvars1=%nvars(&&dsn&_j_); nobs2=%nobs(&&dsn&_k_); nvars2=%nvars(&&dsn&_k_); nvar_in1_un2=%nobs(Names_InData&_j_._NotInData&_k_); nvar_un1_in2=%nobs(Names_NotInData&_j_._InData&_k_); put /; put @1 "%justify(%initcaps(Summary of Data: &&dsn&_j_ and &&dsn&_k_), justify=center)" /; put &linesize*"-"/; put @5 "Number of observations in data &&dsn&_j_" @(63+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nobs1 /; put @5 "Number of variables in data &&dsn&_j_" @(63+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nvars1 /; put @5 "Number of observations in data &&dsn&_k_" @(63+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nobs2 /; put @5 "Number of variables in data &&dsn&_k_" @(63+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nvars2 /; put @5 "Number of variables in data &&dsn&_j_ but Not In data &&dsn&_k_" @(63+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nvar_in1_un2 /; put @5 "Number of variables Not in data &&dsn&_j_ but In data &&dsn&_k_" @(63+max(length("&&dsn&_j_"),length("&&dsn&_k_"))) nvar_un1_in2 /; run; %put %_repeat('-',&linesize); %if (%quote(&Names_Ind1_NotInd2) ne) %then %do; %put --> Note: Variables in %upcase(&&dsn&_j_) and not in %upcase(&&dsn&_k_): ; %put --> %upcase(&Names_Ind1_NotInd2)%str(;); %put --> please see output for label details.; %end; %else %do; %put --> Note: All variables in %upcase(&&dsn&_j_) are in %upcase(&&dsn&_k_).; %end; %put %_repeat('-',&linesize); %if (%quote(&Names_NotInd1_Ind2) ne) %then %do; %put --> Note: Variables not in %upcase(&&dsn&_j_) but in %upcase(&&dsn&_k_): ; %put --> %upcase(&Names_NotInd1_Ind2)%str(;); %put --> please see output for label details.; %end; %else %do; %put --> Note: All variables in %upcase(&&dsn&_k_) are in %upcase(&&dsn&_j_). ; %end; %put %_repeat('-',&linesize); /*** cleanup the temp datasets ***/ proc datasets library=work nolist; delete Names_Indata&_j_._NotIndata&_k_ Names_NotIndata&_j_._Indata&_k_; run;quit; %end; %end; proc datasets library=work nolist; delete _tmp&_j_; run;quit; %end; proc datasets library=work nolist; delete _tmp&ndsn; run;quit; %end; %finish: %let syslast=&_tmplast_; %mend vcompare;